<?php

namespace App\Models;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Encore\Admin\Facades\Admin;


class Crousesheet extends Model
{
    public $timestamps = false;
    //获取教室
    public function getClassroom(){
        $classrooms = DB::table('classrooms')->orderBy('name','ASC')->get(['id','name'])->toArray();
        return $classrooms;
    }

    //获取时间档案
    public function getTime(){
        $time = DB::table('crouse_times')->get(['start_time','end_time','id'])->toArray();
        return $time;
    }

    public function getSquads($tutor_id){
        $squads = DB::table('squads')->where('class_teacher',$tutor_id)
            ->get(['id','name'])->toArray();
        return $squads;
    }

    //获取老师
    public function getTeacher(){
        $ids = DB::table('admin_roles')->where('name','教师')->get()->toArray();
        $idArr = array();

        foreach($ids as $id){
            $idArr[] = $id->id;
        }
        $teacher = DB::table('admin_users as au')->join('admin_role_users as aru','au.id','=','aru.user_id')
            ->whereIn('aru.role_id',$idArr)->get(['au.id','au.en_name'])->toArray();
        return $teacher;
    }

    //班主任
    public function getTutor(){
        $ids = DB::table('admin_roles')->where('name','班主任')->get()->toArray();
        $idArr = array();

        foreach($ids as $id){
            $idArr[] = $id->id;
        }
        $Tutor = DB::table('admin_users as au')->join('admin_role_users as aru','au.id','=','aru.user_id')
            ->whereIn('aru.role_id',$idArr)->get(['au.id','au.en_name'])->toArray();
        return $Tutor;
    }

    //获取课程
    public function getCrouse(){
        $time = DB::table('crouses')->get(['id','name'])->toArray();
        return $time;
    }

    //获取课程表
    public function getCrousesheet(int $section = 1,$action = 1){
        if($action == 2){
            $query = DB::table('set_crousesheets as cs');
            $crouse = $query
                ->join('classrooms as cr','cs.classroom_id','=','cr.id')
                ->join('crouses as c','cs.crouse_id', '=', 'c.id')
                ->join('admin_users as t1','cs.teacher_id', '=', 't1.id')
                ->join('admin_users as t2','cs.tutor_id', '=', 't2.id')
                ->join('squads as s','cs.squads_id','=','s.id')
                ->where('cs.section',$section)
                ->select('cs.*','cr.name as classname','t1.en_name as teacher_name','t2.en_name as tutor_name','c.name as crousename','s.name as squad_name')->get()->toArray();
        }else{
            $query = DB::table('crousesheets as cs');
            $crouse = $query
                ->join('classrooms as cr','cs.classroom_id','=','cr.id')
                ->join('crouses as c','cs.crouse_id', '=', 'c.id')
                ->join('admin_users as t1','cs.teacher_id', '=', 't1.id')
                ->join('admin_users as t2','cs.tutor_id', '=', 't2.id')
                ->join('squads as s','cs.squads_id','=','s.id')
                ->where('cs.section',$section)->where('cs.status','=',1)
                ->select('cs.*','cr.name as classname','t1.en_name as teacher_name','t2.en_name as tutor_name','c.name as crousename','s.name as squad_name')->get()->toArray();
        }
        return $crouse;
    }

    //获取个人课程表
    public function getTeachersheet(int $t_id = 0){
        $crouse = DB::table('crousesheets as cs')
            ->join('classrooms as cr','cs.classroom_id','=','cr.id')
            ->join('crouses as c','cs.crouse_id', '=', 'c.id')
            ->join('admin_users as t1','cs.teacher_id', '=', 't1.id')
            ->join('admin_users as t2','cs.tutor_id', '=', 't2.id')
            ->join('squads as s','s.id','=','cs.squads_id')
            ->where('cs.status','=',1)->where('cs.teacher_id',$t_id)->orWhere('cs.tutor_id',$t_id)
            ->select('cs.*','cr.name as classname','t1.en_name as teacher_name','t2.en_name as tutor_name','c.name as crousename','s.name as squad_name')->get()->toArray();
        return $crouse;
    }

    //获取当天课程数
    public function getNum(int $section = 0,int $id = 0){
        if($section == 0){
            $section = 7;
        }
        $num = DB::table('crousesheets')->where('section',$section)->where('status','1')
            ->where(function(Builder $builder){
                $builder->where('teacher_id',Admin::user()->id)->orWhere('tutor_id',Admin::user()->id);
            })->count();
        return $num;
    }


    //创建课程表
    public function createSheet(array $room,int $section=1){
        $sheetArr=$sheet=array();
        $crouses = $this->getCrousesheet($section);

        $temp_arr = array();
        foreach($crouses as $k=>$v){
            $temp_arr[$v->position] = $v;
        }
        $i=$j=1;

        //数据拼装
        foreach($room as $v){
            $row = $temp_row=array();
            $i=$v->id;
            for($j=1;$j<=13;$j++){
                if(isset($temp_arr[$i.'-'.$j])){
                    $temp_row['content'] = $temp_arr[$i.'-'.$j]->crousename.'<br />'.$temp_arr[$i.'-'.$j]->teacher_name.'/'.
                        $temp_arr[$i.'-'.$j]->tutor_name.'<br />'.$temp_arr[$i.'-'.$j]->classname
                            .'&nbsp;&nbsp;&nbsp;&nbsp;'.$temp_arr[$i.'-'.$j]->squad_name
                        .'<br />'.$temp_arr[$i.'-'.$j]->start_time.'-'.$temp_arr[$i.'-'.$j]->end_time.'<br />'.$temp_arr[$i.'-'.$j]->memo;
                    $temp_row['attribute'] = 'class="classes" id="'.$temp_arr[$i.'-'.$j]->position.'" teacher_id ="'.$temp_arr[$i.'-'.$j]->teacher_id.'"
                     crouse_id="'.$temp_arr[$i.'-'.$j]->crouse_id.'"'.'tutor_id = "'.$temp_arr[$i.'-'.$j]->tutor_id.'"
                     class_id = "'.$temp_arr[$i.'-'.$j]->squads_id.'" start_time="'.$temp_arr[$i.'-'.$j]->start_time.'"
                     end_time="'.$temp_arr[$i.'-'.$j]->end_time.'"';
                    if($temp_arr[$i.'-'.$j]->crousename == '单词口语课'){
                        $temp_row['attribute'] .= ' style="background-color:#c0e49d;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '应用口语课'){
                        $temp_row['attribute'] .= ' style="background-color:#fad99f;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '语境课'){
                        $temp_row['attribute'] .= ' style="background-color:#ff9781;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '阅读课'){
                        $temp_row['attribute'] .= ' style="background-color:#88ccff;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '写作课'){
                        $temp_row['attribute'] .= ' style="background-color:#98efea;width:50px"';
                    }
                }else{
                    $temp_row['content'] = '';
                    $temp_row['attribute'] = 'class="classes" style="width:50px" id="'.$i.'-'.$j.'"';
                }
                $row[] = $temp_row;
            }
            $temp_row['content'] = $v->name;
            $temp_row['attribute'] = 'class="room" style="width:50px" id="'.$v->id.'"';
            array_unshift($row,$temp_row);
            $sheetArr[] = $row;
            //$i++;
        }

        return $sheetArr;
    }

    public function createSetSheet(array $room,int $section=1){
        $sheetArr=$sheet=array();
        $crouses = $this->getCrousesheet($section,2);

        $temp_arr = array();
        foreach($crouses as $k=>$v){
            $temp_arr[$v->position] = $v;
        }
        $i=$j=1;

        //数据拼装
        foreach($room as $v){
            $row = $temp_row=array();
            $i=$v->id;
            for($j=1;$j<=13;$j++){
                if(isset($temp_arr[$i.'-'.$j])){
                    $temp_row['content'] = $temp_arr[$i.'-'.$j]->crousename.'<br />'.$temp_arr[$i.'-'.$j]->teacher_name.'/'.
                        $temp_arr[$i.'-'.$j]->tutor_name.'<br />'.$temp_arr[$i.'-'.$j]->classname
                        .'&nbsp;&nbsp;&nbsp;&nbsp;'.$temp_arr[$i.'-'.$j]->squad_name
                        .'<br />'.$temp_arr[$i.'-'.$j]->start_time.'-'.$temp_arr[$i.'-'.$j]->end_time.'<br />'.$temp_arr[$i.'-'.$j]->memo;
                    $temp_row['attribute'] = 'class="classes" id="'.$temp_arr[$i.'-'.$j]->position.'" teacher_id ="'.$temp_arr[$i.'-'.$j]->teacher_id.'"
                     crouse_id="'.$temp_arr[$i.'-'.$j]->crouse_id.'"'.'tutor_id = "'.$temp_arr[$i.'-'.$j]->tutor_id.'"
                     class_id = "'.$temp_arr[$i.'-'.$j]->squads_id.'" start_time="'.$temp_arr[$i.'-'.$j]->start_time.'"
                     end_time="'.$temp_arr[$i.'-'.$j]->end_time.'"';
                    if($temp_arr[$i.'-'.$j]->crousename == '单词口语课'){
                        $temp_row['attribute'] .= ' style="background-color:#c0e49d;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '应用口语课'){
                        $temp_row['attribute'] .= ' style="background-color:#fad99f;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '语境课'){
                        $temp_row['attribute'] .= ' style="background-color:#ff9781;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '阅读课'){
                        $temp_row['attribute'] .= ' style="background-color:#88ccff;width:50px"';
                    }elseif($temp_arr[$i.'-'.$j]->crousename == '写作课'){
                        $temp_row['attribute'] .= ' style="background-color:#98efea;width:50px"';
                    }
                }else{
                    $temp_row['content'] = '';
                    $temp_row['attribute'] = 'class="classes" style="width:50px" id="'.$i.'-'.$j.'"';
                }
                $row[] = $temp_row;
            }
            $temp_row['content'] = $v->name;
            $temp_row['attribute'] = 'class="room" style="width:50px" id="'.$v->id.'"';
            array_unshift($row,$temp_row);
            $sheetArr[] = $row;
            //$i++;
        }

        return $sheetArr;
    }

    public function saveCrouse($request)
    {
        if(!in_array($request['section'],array('1','2','3','4','5','6','7'))){
            return array('status'=>-1,'message'=>trans('admin.error'),'data'=>'');
        }


        //判断课程是否冲突
        //共计三种情况：1.老师上课时间交叉；2.同一个班上课时间交叉；3.一个班主任带的两个班上课时间交叉，且没有语境课

        //判断第一种情况
        $exists = DB::table('crousesheets as cs')->where('cs.section',$request['section'])
            ->where('cs.position','!=',$request['position'])
            ->where('cs.teacher_id',$request['teacher'])
            ->where('cs.status','=',1)
            ->select('cs.*')->get()->toArray();

        $begin = strtotime($request['start_time']);
        $end = strtotime($request['end_time']);
        foreach($exists as $v){
            $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
            if($a == true){
                return array('status'=>-1,'message'=>'当前老师该时段已经有课，请检查！冲突位置：'.$v->position);
            }
        }
        //第一种情况判断结束

        //判断第二种情况
        $exists = DB::table('crousesheets as cs')->where('cs.section',$request['section'])
            ->where('cs.position','!=',$request['position'])
            ->where('cs.squads_id',$request['class_id'])
            ->where('cs.status','=',1)
            ->select('cs.*')->get()->toArray();
        foreach($exists as $v){
            $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
            if($a == true){
                return array('status'=>-1,'message'=>'当前班级该时段已经有课，请检查！冲突位置：'.$v->position);
            }
        }
        //第二种情况判断结束


        //判断第三种情况
        $num = DB::table('crouses')->where('id',$request['crouse'])->where('name','LIKE','%语境课%')->count();
        if($num <= 0){
            //前两个条件满足的条件下，添加一万节语境课都可以
            //添加的课是语境课则略过校验，否则校验
            $exists = DB::table('crousesheets as cs')->where('cs.section',$request['section'])
                ->join('crouses as c','c.id','=','cs.crouse_id')
                ->where('cs.position','!=',$request['position'])
                ->where('cs.tutor_id',$request['tutor'])
                ->where('cs.status','=',1)
                ->select('cs.*','c.name')->get()->toArray();

            foreach($exists as $v){
                if($v->name == '语境课'){
                    continue;
                }
                $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
                if($a == true){
                    return array('status'=>-1,'message'=>'当前班主任该时段已经有课，请检查！冲突位置:'.$v->position);
                }
            }
        }
        //第三种情况判断结束

        $record = DB::table('crousesheets')->where('section',$request['section'])
            ->where('position',$request['position'])->where('status','=',1)->get()->toArray();


        //tag用来判断是否存在
        $tag = 0;
        if(empty($record)){
            $tag = 1;
        }else{
            foreach($record as $row){
                if(!isset($row->teacher_id) || $row->teacher_id <= 0 || $row->teacher_id ==''){
                    $tag = 1;
                    break;
                }else{
                    $tag = 2;
                    break;
                }
            }
        }


        if($tag == 2){
            //如果存在则更新
            DB::table('crousesheets')->where('section',$request['section'])
                ->where('position',$request['position'])
                ->update(['crouse_id'=>$request['crouse'],'classroom_id'=>$request['classroom_id'],
                'teacher_id'=>$request['teacher'],'memo'=>$request['memo'],'tutor_id'=>$request['tutor'],
                    'squads_id'=>$request['class_id'],'period'=>$request['period'],'start_time'=>$request['start_time']
                ,'end_time'=>$request['end_time'],'status'=>1]);
        }elseif($tag == 1){
            //不存在则插入
            DB::table('crousesheets')->insert(['crouse_id'=>$request['crouse'],'classroom_id'=>$request['classroom_id'],
                'teacher_id'=>$request['teacher'],'memo'=>$request['memo'],'tutor_id'=>$request['tutor'],
                'position'=>$request['position'],'section'=>$request['section'],
                'squads_id'=>$request['class_id'],'period'=>$request['period'],'start_time'=>$request['start_time']
                ,'end_time'=>$request['end_time'],'status'=>1]);
        }

        $crouse = DB::table('crousesheets as cs')
            ->join('classrooms as cr','cs.classroom_id','=','cr.id')
            ->join('crouses as c','cs.crouse_id', '=', 'c.id')
            ->join('admin_users as t1','cs.teacher_id', '=', 't1.id')
            ->join('admin_users as t2','cs.tutor_id','=','t2.id')
            ->join('squads as s','cs.squads_id','=','s.id')
            ->where('cs.section',$request['section'])
            ->where('cs.position',$request['position'])
            ->where('cs.status','=',1)
            ->select('cs.*','cr.name as classname','t1.en_name as teacher_name','t2.en_name as tutor_name','c.name as crousename','s.name as squad_name')->get()->toArray();

        return array('status'=>1,'message'=>trans('admin.success'),'data'=>$crouse);
    }

    public function saveSetCrouse($request)
    {
        if(!in_array($request['section'],array('1','2','3','4','5','6','7'))){
            return array('status'=>-1,'message'=>trans('admin.error'),'data'=>'');
        }


        //判断课程是否冲突
        //共计三种情况：1.老师上课时间交叉；2.同一个班上课时间交叉；3.一个班主任带的两个班上课时间交叉，且没有语境课

        //判断第一种情况
        $exists = DB::table('set_crousesheets as cs')->where('cs.section',$request['section'])
            ->where('cs.position','!=',$request['position'])
            ->where('cs.teacher_id',$request['teacher'])
            ->select('cs.*')->get()->toArray();

        $begin = strtotime($request['start_time']);
        $end = strtotime($request['end_time']);
        foreach($exists as $v){
            $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
            if($a == true){
                return array('status'=>-1,'message'=>'当前老师该时段已经有课，请检查！','position'=>array($v->position,$request['position']));
            }
        }
        //第一种情况判断结束

        //判断第二种情况
        $exists = DB::table('set_crousesheets as cs')->where('cs.section',$request['section'])
            ->where('cs.position','!=',$request['position'])
            ->where('cs.squads_id',$request['class_id'])
            ->select('cs.*')->get()->toArray();
        foreach($exists as $v){
            $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
            if($a == true){
                return array('status'=>-1,'message'=>'当前班级该时段已经有课，请检查！','position'=>array($v->position,$request['position']));
            }
        }
        //第二种情况判断结束


        //判断第三种情况
        $num = DB::table('crouses')->where('id',$request['crouse'])->where('name','LIKE','%语境课%')->count();
        if($num <= 0){
            //前两个条件满足的条件下，添加一万节语境课都可以
            //添加的课是语境课则略过校验，否则校验
            $exists = DB::table('set_crousesheets as cs')->where('cs.section',$request['section'])
                ->join('crouses as c','c.id','=','cs.crouse_id')
                ->where('cs.position','!=',$request['position'])
                ->where('cs.tutor_id',$request['tutor'])
                ->select('cs.*','c.name')->get()->toArray();

            foreach($exists as $v){
                if($v->name == '语境课'){
                    continue;
                }
                $a = $this->is_time_cross($begin,$end,strtotime($v->start_time),strtotime($v->end_time));
                if($a == true){
                    return array('status'=>-1,'message'=>'当前班主任该时段已经有课，请检查！','position'=>array($v->position,$request['position']));
                }
            }
        }
        //第三种情况判断结束

        $record = DB::table('set_crousesheets')->where('section',$request['section'])
            ->where('position',$request['position'])->get()->toArray();


        //tag用来判断是否存在
        $tag = 0;
        if(empty($record)){
            $tag = 1;
        }else{
            foreach($record as $row){
                if(!isset($row->teacher_id) || $row->teacher_id <= 0 || $row->teacher_id ==''){
                    $tag = 1;
                    break;
                }else{
                    $tag = 2;
                    break;
                }
            }
        }


        if($tag == 2){
            //如果存在则更新
            DB::table('set_crousesheets')->where('section',$request['section'])
                ->where('position',$request['position'])
                ->update(['crouse_id'=>$request['crouse'],'classroom_id'=>$request['classroom_id'],
                    'teacher_id'=>$request['teacher'],'memo'=>$request['memo'],'tutor_id'=>$request['tutor'],
                    'squads_id'=>$request['class_id'],'period'=>$request['period'],'start_time'=>$request['start_time']
                    ,'end_time'=>$request['end_time']]);
        }elseif($tag == 1){
            //不存在则插入
            DB::table('set_crousesheets')->insert(['crouse_id'=>$request['crouse'],'classroom_id'=>$request['classroom_id'],
                'teacher_id'=>$request['teacher'],'memo'=>$request['memo'],'tutor_id'=>$request['tutor'],
                'position'=>$request['position'],'section'=>$request['section'],
                'squads_id'=>$request['class_id'],'period'=>$request['period'],'start_time'=>$request['start_time']
                ,'end_time'=>$request['end_time']]);
        }

        $crouse = DB::table('set_crousesheets as cs')
            ->join('classrooms as cr','cs.classroom_id','=','cr.id')
            ->join('crouses as c','cs.crouse_id', '=', 'c.id')
            ->join('admin_users as t1','cs.teacher_id', '=', 't1.id')
            ->join('admin_users as t2','cs.tutor_id','=','t2.id')
            ->join('squads as s','cs.squads_id','=','s.id')
            ->where('cs.section',$request['section'])
            ->where('cs.position',$request['position'])
            ->select('cs.*','cr.name as classname','t1.en_name as teacher_name','t2.en_name as tutor_name','c.name as crousename','s.name as squad_name')->get()->toArray();

        return array('status'=>1,'message'=>trans('admin.success'),'data'=>$crouse);
    }

    public static function options($id)
    {
        return static::where('Code', $id)->get()->map(function ($country) {

            return [$country->Code => $country->Name];

        })->flatten();
    }


    function is_time_cross($beginTime1 = '', $endTime1 = '', $beginTime2 = '', $endTime2 = '')
    {
        $status = $beginTime2 - $beginTime1;
        if ($status > 0)
        {
            $status2 = $beginTime2 - $endTime1;
            if ($status2 >= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        else
        {
            $status2 = $endTime2 - $beginTime1;
            if ($status2 > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
}
